package org.jiuling.office.poi.excel;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jiuling.basics.converter.StringConvert;
import org.jiuling.basics.validation.Assertions;
import org.jiuling.basics.validation.IntegerVerify;
import org.jiuling.basics.validation.ObjectVerify;
import org.jiuling.office.poi.exception.PoiException;
import org.jiuling.office.poi.model.ExcelDataModel;

import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 表格读取
 * @author 紫灵
 * @date 2024年08月13日 23时59分45秒
 */
public class ExcelRead {

    public ExcelDataModel<Map<Integer,Object>> readData(InputStream excelInputStream, Integer sheetNum, Integer startRowNum, Integer endRowNum) throws IOException, PoiException {
        if (IntegerVerify.isNull(startRowNum)){
            startRowNum = 0;
        }
        if (IntegerVerify.isNull(sheetNum)){
            sheetNum = 0;
        }
        // 创建workbook对象
        XSSFWorkbook workbook = new XSSFWorkbook(excelInputStream);
        return handleWorkbook(workbook,sheetNum,startRowNum,endRowNum);
    }

    public ExcelDataModel<Map<Integer,Object>> readData(String path, Integer sheetNum, Integer startRowNum, Integer endRowNum) throws IOException, PoiException {
        if (IntegerVerify.isNull(startRowNum)){
            startRowNum = 0;
        }
        if (IntegerVerify.isNull(sheetNum)){
            sheetNum = 0;
        }
        // 创建workbook对象
        XSSFWorkbook workbook = new XSSFWorkbook(path);
        return handleWorkbook(workbook,sheetNum,startRowNum,endRowNum);
    }

    /**
     * 处理工作簿
     * @param workbook 工作簿
     * @param sheetNum 工作表编号
     * @param startRowNum 开始行号
     * @param endRowNum 结束行号
     * @return Excel数据模型
     * @throws PoiException poi异常
     */
    private ExcelDataModel<Map<Integer,Object>> handleWorkbook(XSSFWorkbook workbook,Integer sheetNum, Integer startRowNum, Integer endRowNum) throws PoiException {
        ExcelDataModel<Map<Integer, Object>> excelDataModel = new ExcelDataModel<>();
        XSSFSheet xssfSheet = workbook.getSheetAt(sheetNum);
        excelDataModel.setMaxRowNum(getRowNum(xssfSheet, false));
        // 判断结束行号是否为空，如果为空则录入最大行号
        if (IntegerVerify.isNull(endRowNum)) {
            endRowNum = excelDataModel.getMaxRowNum();
        }
        excelDataModel.setEffectiveRowNum(getRowNum(xssfSheet, true));
        Assertions.isTrue("开始行数不能大于结束行数或者总行数", startRowNum <= endRowNum);

        List<Map<Integer, Object>> mapList = new ArrayList<>();
        // 开始获取数据
        for (int currentRowNum = startRowNum; currentRowNum < endRowNum; currentRowNum++) {
            // 获取行
            XSSFRow row = xssfSheet.getRow(currentRowNum);
            if (ObjectVerify.isNull(row)) continue;
            // 获取最最大列号
            int maxCellNum = row.getPhysicalNumberOfCells();
            Map<Integer, Object> map = new HashMap<>();
            for (int currentCellNum = 0; currentCellNum < maxCellNum; currentCellNum++) {
                map.put(currentCellNum, getCellValue(row, currentCellNum));
            }
            mapList.add(map);
        }
        excelDataModel.setDataList(mapList);
        return excelDataModel;
    }

    /**
     * 获取行号
     * @param sheetNum 工作簿
     * @param effective 是否获取有效行号
     * @return 行号
     * @throws IOException IO流异常
     */
    public int getRowNum(InputStream excelInputStream,int sheetNum,boolean effective) throws IOException {
        XSSFWorkbook workbook = new XSSFWorkbook(excelInputStream);
        XSSFSheet xssfSheet = workbook.getSheetAt(sheetNum);
        int rowNum = getRowNum(xssfSheet,effective);
        excelInputStream.reset();
        return rowNum;
    }

    /**
     * 获取行号
     * @param xssfSheet 工作簿
     * @param effective 是否获取有效行号
     * @return 行号
     */
    private int getRowNum(XSSFSheet xssfSheet,boolean effective){
        // 最大行号
        int rowNum = xssfSheet.getPhysicalNumberOfRows();
        // 是否获取有效行号
        if (effective){
            int effectiveRowNum = 0;
            for (int i = 0; i < rowNum; i++){
                XSSFRow row = xssfSheet.getRow(i);
                if (!ObjectVerify.isNull(row)){
                    // 判断首列是否为空
                    XSSFCell cell = row.getCell(0);
                    if(!ObjectVerify.isNull(cell) && cell.getCellType() != CellType.BLANK) effectiveRowNum++;
                }
            }
            rowNum = effectiveRowNum;
        }
        return rowNum;
    }

    /**
     * 获取列数据
     * @param row 行
     * @param cellNum 列号
     * @return 数据
     */
    private static Object getCellValue(Row row, int cellNum) throws PoiException {
        Cell cell = row.getCell(cellNum);
        if(ObjectVerify.isNull(cell)){
            return null;
        }
        // 获取数据类型
        CellType cellType = cell.getCellType();
        Object value;
        try {
            switch (cellType) {
                case NUMERIC:
                    value = cell.getDateCellValue();
                    break;
                case STRING:
                case FORMULA:
                    value = cell.getStringCellValue();
                    break;
                case BLANK:
                    value = null;
                    break;
                default:
                    throw new PoiException(StringConvert.format("未知字段类型{}！行：{},列：{}", "{}", cellType.name(), row.getRowNum(), cellNum));
            }
        }catch (IllegalStateException e){
            throw new PoiException(StringConvert.format("未知字段类型{}！行：{},列：{}", "{}", cellType.name(), row.getRowNum(), cellNum));
        }
        return value;
    }
}
